
import pandas as pd 
import numpy as np
from lxml import etree
import os
import re
import warnings
import matplotlib.pyplot as plt
import matplotlib.colors as mcolors
import plotly.express as px
import plotly.io as pio
import nltk
from nltk.corpus import stopwords
import string
nltk.download('punkt')
nltk.download('stopwords')
stop_words = set(stopwords.words('english'))

[nltk_data] Downloading package punkt to
[nltk_data]     C:\Users\Jiaqi\AppData\Roaming\nltk_data...
[nltk_data]   Package punkt is already up-to-date!
[nltk_data] Downloading package stopwords to
[nltk_data]     C:\Users\Jiaqi\AppData\Roaming\nltk_data...
[nltk_data]   Package stopwords is already up-to-date!


# warnings.filterwarnings("ignore", category=UserWarning)


pd.set_option('display.max_colwidth', -1)

C:\Users\Jiaqi\AppData\Local\Temp\ipykernel_10520\3652881321.py:1: FutureWarning: Passing a negative integer is deprecated in version 1.0 and will not be supported in future version. Instead, use None to not limit the column width.
  pd.set_option('display.max_colwidth', -1)


pd.set_option('display.max_rows', None)
# pd.set_option('display.max_rows', 10)


# parse the xml file without namespaces

def parse_xml_to_df(xml_file):
    
    try:
        # Parse the XML file
        tree = etree.parse(xml_file)
        root = tree.getroot()
        
        # Get the filename without the extension
        filename = os.path.basename(xml_file)

        # Create a list to store the data
        data = []

        # Iterate over all elements in the XML file
        for element in root:
            # Create a dictionary to store the data for each element
            element_data = {}
            
            # add the filename
            element_data['source_filename'] = filename
            
            ## extract id
            eadid = root.find('.//eadid')
            if eadid is not None:
                element_data['ead_id'] = eadid.text.strip()  # Add strip() to remove leading and trailing white space
            
            publicid = eadid.get('publicid')
            if publicid is not None:
                result = re.search(r'::(.*)\.xml', publicid)
                if result:
                    public_id = result.group(1).split('::')[-1]
                    element_data['public_id'] = public_id    
            
            # Extract titleproper
            titleproper = root.find('.//titleproper')
            if titleproper is not None:
                element_data['titleproper'] = titleproper.text
            
            
            ## EXtract abstract
            abstract = element.find('.//abstract')
            if abstract is not None:
                element_data['abstract'] = abstract.text

            ## Extract language
            language = element.find('.//langmaterial')
            if language is not None:
                element_data['language'] = ''.join(language.itertext())

            ## Extract scopecontent
            scopecontent = element.findall('./scopecontent')
            if scopecontent:
                scopecontent_texts = []
                for sc in scopecontent:
                    paragraphs = sc.findall('./p')
                    if paragraphs:
                        for p in paragraphs:
                            p_text = ""
                            for child in p.itertext():
                                p_text += child
                            scopecontent_texts.append(p_text)
                element_data['scopecontent'] = '; '.join(scopecontent_texts)
            
            ## Extract controlaccess
            controlaccess = element.find('.//controlaccess')
            if controlaccess is not None:
                subjects = controlaccess.findall('.//subject')
                if subjects:
                    element_data['subjects'] = '; '.join([subject.text for subject in subjects])
                    # Extract the 'source' attribute for each 'subject' tag
                    element_data['subjects_source'] = '; '.join([subject.get('source') for subject in subjects if subject.get('source') is not None])

                genreforms = controlaccess.findall('.//genreform')
                if genreforms:
                    element_data['genreforms'] = '; '.join([genreform.text for genreform in genreforms])
                    # Extract the 'source' attribute for each 'genreform' tag
                    element_data['genreforms_source'] = '; '.join([genreform.get('source') for genreform in genreforms if genreform.get('source') is not None])

                geognames = controlaccess.findall('.//geogname')
                if geognames:
                    element_data['geognames'] = '; '.join([geogname.text for geogname in geognames])
                    # Extract the 'source' attribute for each 'geogname' tag
                    element_data['geognames_source'] = '; '.join([geogname.get('source') for geogname in geognames if geogname.get('source') is not None])

                persnames = controlaccess.findall('.//persname')
                if persnames:
                    element_data['persnames'] = '; '.join([persname.text for persname in persnames])
                    # Extract the 'source' attribute for each 'persname' tag
                    element_data['persnames_source'] = '; '.join([persname.get('source') for persname in persnames if persname.get('source') is not None])

                corpnames = controlaccess.findall('.//corpname')
                if corpnames:
                    element_data['corpnames'] = '; '.join([corpname.text for corpname in corpnames])
                    # Extract the 'source' attribute for each 'corpname' tag
                    element_data['corpnames_source'] = '; '.join([corpname.get('source') for corpname in corpnames if corpname.get('source') is not None])

                famnames = controlaccess.findall('.//famname')
                if famnames:
                    element_data['famnames'] = '; '.join([famname.text for famname in famnames])
                    # Extract the 'source' attribute for each 'famname' tag
                    element_data['famnames_source'] = '; '.join([famname.get('source') for famname in famnames if famname.get('source') is not None])

            ## Extract bioghist    
            bioghist = element.findall('./bioghist')
            if bioghist:
                bioghist_texts = []
                for bio in bioghist:
                    paragraphs = bio.findall('./p')
                    if paragraphs:
                        for p in paragraphs:
                            p_text = ""
                            for child in p.itertext():
                                p_text += child
                            bioghist_texts.append(p_text)
                element_data['bioghist'] = '; '.join(bioghist_texts)

            ## Extract custodhist
            custodhist = element.findall('./custodhist')
            if custodhist:
                custodhist_texts = []
                for cus in custodhist:
                    paragraphs = cus.findall('./p')
                    if paragraphs:
                        for p in paragraphs:
                            p_text = ""
                            for child in p.itertext():
                                p_text += child
                            custodhist_texts.append(p_text)
                element_data['custodhist'] = '; '.join(custodhist_texts)

            # Add the element data to the list of data
            data.append(element_data)

        # print(data)
        
        df = pd.DataFrame([d for d in data if len(d)>4])

    except:
        # If error, print the error message and skip the file
        print("Error parsing file:", xml_file)
        df = None
    
    return df


# Example for SCRC files:

namespaces = {
    "ead": "urn:isbn:1-931666-22-9",
    "xlink": "http://www.w3.org/1999/xlink",
    "xsi": "http://www.w3.org/2001/XMLSchema-instance"
}


# Parse xml file with namespaces - (FOR SCRC files in our example)

def parse_xml_to_df_ns(xml_file):
    try:
        
        # Parse the XML file
        tree = etree.parse(xml_file)
        root = tree.getroot()
        
        # Get the filename without the extension
        filename = os.path.basename(xml_file)

        # Create a list to store the data
        data = []

        # Iterate over all elements in the XML file
        for element in root:
            # Create a dictionary to store the data for each element
            element_data = {}

            # add the filename
            element_data['source_filename'] = filename
        
            ## extract id
            eadid = root.find('.//ead:eadid', namespaces)
            if eadid is not None:
                element_data['ead_id'] = eadid.text.strip()  # Add strip() to remove leading and trailing white space

            publicid = eadid.get('publicid')
            if publicid is not None:
                result = re.search(r'::(.*)\.xml', publicid)
                if result:
                    public_id = result.group(1).split('::')[-1]
                    element_data['public_id'] = public_id

                    
            # Extract titleproper
            titleproper = root.find('.//ead:titleproper', namespaces)
            if titleproper is not None:
                element_data['titleproper'] = titleproper.text
                
                
            ## extract abstract
            abstract = element.find('.//ead:abstract', namespaces)
            if abstract is not None:
                element_data['abstract'] = abstract.text
             
            ## Extract language
            language = root.findall('.//ead:langmaterial', namespaces)[-1]
            if language is not None:
                element_data['language'] = ''.join(language.itertext())
                
            ## Extract scopecontent
            scopecontent = element.find('.//ead:scopecontent', namespaces)
            if scopecontent is not None:
                scopecontent_texts = []
                p_elements = scopecontent.findall('.//ead:p', namespaces)
                for p in p_elements:
                    p_text = ""
                    for child in p.itertext():
                        p_text += child
                    scopecontent_texts.append(p_text)
                element_data['scopecontent'] = '; '.join(scopecontent_texts)    

            
            ## Extract bioghist    
            bioghist = element.find('.//ead:bioghist', namespaces)
            if bioghist is not None:
                bioghist_texts = []
                p_elements = bioghist.findall('.//ead:p', namespaces)
                
                for p in p_elements:
                    p_text = ""
                    for child in p.itertext():
                        p_text += child
                    bioghist_texts.append(p_text)
                element_data['bioghist'] = '; '.join(bioghist_texts) 
           
            
            ## Extract custodhist    
            custodhist = element.find('.//ead:custodhist', namespaces)
            if custodhist is not None:
                custodhist_texts = []
                p_elements = custodhist.findall('.//ead:p', namespaces)
                
                for p in p_elements:
                    p_text = ""
                    for child in p.itertext():
                        p_text += child
                    custodhist_texts.append(p_text)
                element_data['custodhist'] = '; '.join(custodhist_texts)
            
            ## Extract controlaccess
            controlaccess = element.find('.//ead:controlaccess', namespaces)
            if controlaccess is not None:
                subjects = controlaccess.findall('.//ead:subject', namespaces)
                if subjects:
                    element_data['subjects'] = '; '.join([subject.text for subject in subjects])
                    element_data['subjects_source'] = '; '.join([subject.get('source') for subject in subjects if subject.get('source') is not None])

                genreforms = controlaccess.findall('.//ead:genreform', namespaces)
                if genreforms:
                    element_data['genreforms'] = '; '.join([genreform.text for genreform in genreforms])
                    element_data['genreforms_source'] = '; '.join([genreform.get('source') for genreform in genreforms if genreform.get('source') is not None])

                geognames = controlaccess.findall('.//ead:geogname', namespaces)
                if geognames:
                    element_data['geognames'] = '; '.join([geogname.text for geogname in geognames])
                    element_data['geognames_source'] = '; '.join([geogname.get('source') for geogname in geognames if geogname.get('source') is not None])

                persnames = controlaccess.findall('.//ead:persname', namespaces)
                if persnames:
                    element_data['persnames'] = '; '.join([persname.text for persname in persnames])
                    element_data['persnames_source'] = '; '.join([persname.get('source') for persname in persnames if persname.get('source') is not None])

                corpnames = controlaccess.findall('.//ead:corpname', namespaces)
                if corpnames:
                    element_data['corpnames'] = '; '.join([corpname.text for corpname in corpnames])
                    element_data['corpnames_source'] = '; '.join([corpname.get('source') for corpname in corpnames if corpname.get('source') is not None])

                famnames = controlaccess.findall('.//ead:famname', namespaces)
                if famnames:
                    element_data['famnames'] = '; '.join([famname.text for famname in famnames])
                    element_data['famnames_source'] = '; '.join([famname.get('source') for famname in famnames if famname.get('source') is not None])
                   
            # Add the element data to the list of data
            data.append(element_data)

        # Create a DataFrame from the list of data
        df = pd.DataFrame([d for d in data if len(d)>4])
        
    except:
        # If error, print the error message and skip the file
        print("Error parsing file:", xml_file)
        df = None

    return df


# # file path

# xml_file_1 = 'RCRC_Finding_Aid_List_Bentley/Finding_Aids/umich-bhl-0052.xml'
# xml_file_2 = 'SCRC_XML/adler_20221006_152012_UTC__ead.xml'
# xml_file_3 = 'Clements_Library_Philippine_Islands_EAD/hillardlow_final.xml'

# # parse one xml file (without namespace)

# df = parse_xml_to_df(xml_file_1)
# df.style.set_properties(**{'text-align': 'left'}) # align text to the left (if needed)


# # parse one xml file (with namespace)

# df = parse_xml_to_df_ns(xml_file_2)
# df.style.set_properties(**{'text-align': 'left'})


# Parsing Multiple XML Files (without namespace)

def parse_xml_folder_to_df(folder_path):
    # Create a list to store the dataframes for each file
    dfs = []
    
    # Loop over all XML files in the folder
    for filename in os.listdir(folder_path):
        if filename.endswith(".xml"):
            file_path = os.path.join(folder_path, filename)
            df = parse_xml_to_df(file_path)
            dfs.append(df)
    
    # Concatenate the dataframes into one dataframe
    result_df = pd.concat(dfs, ignore_index=True)
    
    return result_df

# Parsing Multiple XML Files (with namespace)

def parse_xml_folder_to_df_ns(folder_path):
    # Create a list to store the dataframes for each file
    dfs = []
    
    # Loop over all XML files in the folder
    for filename in os.listdir(folder_path):
        if filename.endswith(".xml"):
            file_path = os.path.join(folder_path, filename)
            df = parse_xml_to_df_ns(file_path)
            dfs.append(df)
    
    # Concatenate the dataframes into one dataframe
    result_df = pd.concat(dfs, ignore_index=True)
    
    return result_df


# select/ change local file path

folder1_path = "RCRC_Finding_Aid_List_Bentley/Finding_Aids"
folder2_path = "Clements_Library_Philippine_Islands_EAD"
folder3_path = "SCRC_XML"


# display parsed data - Bentley 

df1_Bentley = parse_xml_folder_to_df(folder1_path)
# df1_Bentley


# display parsed data - Clements
df2_Clements = parse_xml_folder_to_df(folder2_path)

# df2_Clements


# display parsed data - SCRC 

df3_SCRC = parse_xml_folder_to_df_ns(folder3_path)

# df3_SCRC


# export

df1_Bentley.to_csv('df1_Bentley.csv', index=True)
df2_Clements.to_csv('df2_Clements.csv', index=True)
df3_SCRC.to_csv('df3_SCRC.csv', index=True)


# read in the txt file term list
with open('general_list_by_ella.txt', 'r') as f:
    terms = [line.strip() for line in f]


# check the term list we get from txt
# terms


# match term

def match_terms(row, terms, columns):
    results = []
    for term in terms:
        for col in columns:
            if not isinstance(row[col], float):
                # split the column into paragraphs
                paragraphs = row[col].split('\n')
                # loop through each paragraph
                for paragraph in paragraphs:
                    # check if the term is in the current paragraph
                    if re.search(r'\b' + re.escape(term) + r'\b', paragraph, re.IGNORECASE):
                        results.append({
                            'ead_id': row['ead_id'],
                            'source_filename': row['source_filename'],
                            'titleproper': row.get('titleproper', None), 
                            'Term': term, 
                            'Matched_Times': len(re.findall(r'\b' + re.escape(term) + r'\b', paragraph, re.IGNORECASE)),
                            'Matched_From': col, 
                            'Matched_Paragraph': paragraph
                        })
    return results


def match_and_visualize(df, name):
    # Match results
    results_df = pd.DataFrame([result for index, row in df.iterrows() for result in match_terms(row, terms, df.columns)])
    
    # Show matched results
    print("Matched results for ", name)
    # display(results_df) # control dislay

    # Export to CSV
    results_df.to_csv('matched_results_' + name + '.csv', index=True)
    return results_df  # Return the DataFrame for later use


def calculate_term_frequency(df, df_name):
    term_frequency = df.groupby('Term')['Matched_Times'].sum().reset_index()
    term_frequency.rename(columns={'Matched_Times': 'Total_Frequency'}, inplace=True)
    term_frequency['DataFrame'] = df_name

    # Sort in descending order
    term_frequency.sort_values(by='Total_Frequency', ascending=False, inplace=True)
    
    # Show frequency table
    print("Term frequency for ", df_name)
    display(term_frequency)

    return term_frequency


# for each file pool
file_list = [(df1_Bentley, 'Bentley'), (df2_Clements, 'Clements'), (df3_SCRC, 'SCRC')]
matched_results = {name: match_and_visualize(df, name) for df, name in file_list}

Matched results for  Bentley
Matched results for  Clements
Matched results for  SCRC


def visualize_all(matched_results):
    term_frequencies = pd.concat([calculate_term_frequency(df, name) for name, df in matched_results.items()])

    # Define a color dictionary for each dataframe
    colors = {'Bentley': 'blue', 'Clements': 'red', 'SCRC': 'green'}

    # Visualization
    fig = px.bar(term_frequencies, x='Term', y='Total_Frequency', text='Total_Frequency', 
                 color='DataFrame', color_discrete_map=colors, facet_col='DataFrame')
    fig.update_traces(textposition='outside', insidetextanchor='middle')
    fig.update_layout(title_text="Term Found in DataFrames", xaxis_title_standoff=10, height=600)
    fig.show()
    pio.write_image(fig, 'term_frequency_stacked.png')

    # Variation Visualization
    fig = px.bar(term_frequencies, x='Term', y='Total_Frequency', text='Total_Frequency', 
                 color='DataFrame', color_discrete_map=colors, barmode='group')
    fig.update_traces(textposition='outside', insidetextanchor='middle')
    fig.update_layout(title_text="Term Found in DataFrames", xaxis_title_standoff=10, height=600)
    fig.show()
    pio.write_image(fig, 'term_frequency_side_by_side.png')

visualize_all(matched_results)

Term frequency for  Bentley

Term frequency for  Clements

Term frequency for  SCRC


def calculate_element_frequency(df, df_name):
    element_counts = df.groupby('Matched_From')['Matched_Times'].sum()
    element_counts_sorted = element_counts.sort_values(ascending=False)
    df_element_counts = pd.DataFrame(list(element_counts_sorted.items()), columns=['Subsection', 'harmful_terms_frequency'])
    df_element_counts['Source'] = df_name  # Indicate the source dataframe
    return df_element_counts

# Use the function for each dataframes
element_frequencies_list = [calculate_element_frequency(df, name) for name, df in matched_results.items()]

# Concatenate all element frequencies
all_element_frequencies = pd.concat(element_frequencies_list)

# Show the DataFrame
print("Element frequencies across all file pools:")
display(all_element_frequencies)

# Visualization
fig = px.bar(all_element_frequencies, x='Subsection', y='harmful_terms_frequency', color='Source', text='harmful_terms_frequency')
fig.update_traces(textposition='outside')
fig.update_layout(title_text="Element Frequencies Across DataFrames", xaxis_title="Subsection", yaxis_title="Term Frequency", height=600, barmode='group')
fig.show()
pio.write_image(fig, 'element_frequency_across_dfs.png')

Element frequencies across all file pools:


def calculate_subsection_term_frequency(df, df_name):
    subsection_term_frequency = df.groupby(['Matched_From', 'Term'])['Matched_Times'].sum().reset_index()
    subsection_term_frequency.rename(columns={'Matched_From': 'Subsection', 'Matched_Times': 'Term_Frequency'}, inplace=True)
    subsection_term_frequency['Source'] = df_name
    return subsection_term_frequency

# Use the function for each pool
subsection_term_frequencies_list = [calculate_subsection_term_frequency(df, name) for name, df in matched_results.items()]

# Concatenate all subsection term frequencies
all_subsection_term_frequencies = pd.concat(subsection_term_frequencies_list)


# Treemap
color_map = {'Bentley': 'blue', 'Clements': 'red', 'SCRC': 'green'}
fig = px.treemap(all_subsection_term_frequencies, path=['Source', 'Subsection', 'Term'], values='Term_Frequency')
fig.update_layout(width=1500, height=800)
fig.show()


# Add a dummy color column
all_subsection_term_frequencies['color'] = all_subsection_term_frequencies['Term_Frequency']

# Treemap - color scale
fig = px.treemap(all_subsection_term_frequencies, path=['Source', 'Subsection', 'Term'], values='Term_Frequency',
                 color='color', color_continuous_scale='YlOrRd')
fig.update_layout(width=1500, height=800)
fig.show()


# Sunburst - color scale
fig = px.sunburst(all_subsection_term_frequencies, path=['Source', 'Subsection', 'Term'], values='Term_Frequency', 
                  color='Term_Frequency', color_continuous_scale='YlOrRd')
fig.update_layout(width=1300, height=800)
fig.show()


def plot_source_frequencies(df1, df2, df3, df1_name, df2_name, df3_name, column):
    if column in df1.columns:
        df1_counts = df1[column].fillna('').str.split('; ').explode().value_counts()
    else:
        df1_counts = pd.Series(dtype='int')
        
    if column in df2.columns:
        df2_counts = df2[column].fillna('').str.split('; ').explode().value_counts()
    else:
        df2_counts = pd.Series(dtype='int')
        
    if column in df3.columns:
        df3_counts = df3[column].fillna('').str.split('; ').explode().value_counts()
    else:
        df3_counts = pd.Series(dtype='int')

    # Create a new DataFrame to store these counts
    counts_df = pd.DataFrame({
        df1_name: df1_counts,
        df2_name: df2_counts,
        df3_name: df3_counts
    }).fillna(0)

    # Create a color palette
    cmap = plt.cm.get_cmap('Set3')
    colors = cmap([0.0, 0.5, 1.0])

    # Plot with rotated x-axis labels
    counts_df.plot(kind='bar', figsize=(6, 4), rot=0, color=colors)
    plt.title(f'Source Frequencies for {column}')
    plt.ylabel('Frequency')
    plt.tight_layout()
    plt.show()

# Specify the source columns
source_columns = ['subjects_source', 'genreforms_source', 'geognames_source', 'persnames_source', 'corpnames_source', 'famnames_source']

# Call the function for each source column
for column in source_columns:
    plot_source_frequencies(df1_Bentley, df2_Clements, df3_SCRC, 'df1_Bentley', 'df2_Clements', 'df3_SCRC', column)

Automation Tool for matching terms in local XML files¶

Import packages and settings¶

Note on Packages¶

Parsing XML to DataFrame¶

In this script, we cater to two common types of XML files:¶

1. XML files without namespaces.¶

2. XML files with namespaces.¶

Depending on the structure of your XML files and the namespaces used, you might need to tailor the respective functions. Please adjust these functions according to the type of XML files you are working with to ensure optimal results.¶

Defining Namespace Prefix and URI¶

An example: Parsing Single XML File¶

Parsing Multiple XML Files¶

Selecting and Parsing XML Files from Local Directories¶

Exporting DataFrames to CSV Files¶

Term Matching¶

Matching Results and Visualizations¶

Identifying Elements with the Highest Term Occurrences¶

Visualizing Term Frequencies Across Subsections¶

Visualizing Source Frequencies¶

	Term	Total_Frequency	DataFrame
10	chairman	70	Bentley
9	black	49	Bentley
18	white	36	Bentley
15	man	19	Bentley
6	Prisoners	6	Bentley
1	Colored	5	Bentley
14	jock	5	Bentley
16	native american	4	Bentley
13	indigenous	3	Bentley
11	foreman	2	Bentley
17	the elderly	2	Bentley
3	Handicapped	1	Bentley
4	Leprosy	1	Bentley
5	Oriental	1	Bentley
7	aboriginal	1	Bentley
8	alcoholic	1	Bentley
2	Criminals	1	Bentley
12	illegal immigrants	1	Bentley
0	Afro-American	1	Bentley

	Term	Total_Frequency	DataFrame
3	Prisoners	23	Clements
15	white	17	Clements
9	man	15	Clements
4	black	13	Clements
12	slave	8	Clements
0	Colored	4	Clements
11	savage	3	Clements
13	slaves	3	Clements
2	Oriental	2	Clements
6	evacuation	2	Clements
8	indigenous	2	Clements
1	Leprosy	1	Clements
5	coolie	1	Clements
7	foreman	1	Clements
10	relocation	1	Clements
14	the poor	1	Clements

	Term	Total_Frequency	DataFrame
7	white	12	SCRC
2	black	8	SCRC
1	alcoholic	7	SCRC
4	indigenous	3	SCRC
0	Prisoners	2	SCRC
5	man	2	SCRC
6	policeman	2	SCRC
3	chairman	1	SCRC

	Subsection	harmful_terms_frequency	Source
0	bioghist	121	Bentley
1	scopecontent	31	Bentley
2	persnames	23	Bentley
3	corpnames	15	Bentley
4	abstract	8	Bentley
5	subjects	6	Bentley
6	genreforms	4	Bentley
7	titleproper	1	Bentley
0	scopecontent	56	Clements
1	bioghist	18	Clements
2	subjects	14	Clements
3	persnames	5	Clements
4	genreforms	2	Clements
5	geognames	2	Clements
0	scopecontent	17	SCRC
1	abstract	7	SCRC
2	bioghist	7	SCRC
3	subjects	6	SCRC